package br.com.vagacerta.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import br.com.vagacerta.modelo.TipoVaga;
import br.com.vagacerta.modelo.Vaga;

@Repository
public class VagaDAO {

	private Connection connection;
	private final SensorDAO sensorDAO;

	@Autowired
	public VagaDAO(DataSource dataSource, SensorDAO sensorDAO) {
		try {
			this.connection = dataSource.getConnection();
			this.sensorDAO = sensorDAO;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public Vaga retornaVagaPorId(long idVaga) {

		try {

			String sql = "SELECT * FROM VAGA WHERE COD_VAGA = ?";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, idVaga);
			ResultSet rs = stmt.executeQuery();
			Vaga vaga = new Vaga();

			while (rs.next()) {

				vaga.setCodigoVaga(rs.getLong("COD_VAGA"));
				vaga.setTipoVaga(retornaTipoVagaPorId(rs
						.getLong("COD_TIPO_VAGA")));
				vaga.setNomeVaga(rs.getString("NOME_VAGA"));
				vaga.setNivelVaga(rs.getInt("NIVEL_VAGA"));
				vaga.setStatusVaga(rs.getShort("STATUS_VAGA"));
				vaga.setSensor(sensorDAO.retornaSensorPorId(rs
						.getLong("COD_SENSOR")));

			}

			return vaga;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public TipoVaga retornaTipoVagaPorId(long idTipoVaga) {

		try {

			String sql = "SELECT * FROM TIPO_VAGA WHERE COD_TIPO_VAGA = ? LIMIT 1";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, idTipoVaga);
			ResultSet rs = stmt.executeQuery();
			TipoVaga tipoVaga = new TipoVaga();

			while (rs.next()) {

				tipoVaga.setCodigoTipoVaga(rs.getLong("COD_TIPO_VAGA"));
				tipoVaga.setDescricaoTipoVaga(rs
						.getString("DESCRICAO_TIPO_VAGA"));

			}

			rs.close();
			stmt.close();

			return tipoVaga;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public List<Vaga> carregaTodasAsVagas() {
		try {
			String sql = "SELECT * FROM VAGA";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			ResultSet rs = stmt.executeQuery();
			List<Vaga> listaVagas = new ArrayList<Vaga>();

			while (rs.next()) {
				Vaga vaga = new Vaga();

				vaga.setCodigoVaga(rs.getLong(("COD_VAGA")));
				vaga.setTipoVaga(retornaTipoVaga(rs.getLong("COD_TIPO_VAGA")));
				vaga.setNivelVaga(rs.getInt("NIVEL_VAGA"));
				vaga.setNomeVaga(rs.getString("NOME_VAGA"));
				vaga.setSensor(sensorDAO.carregaSensorPorNome(rs
						.getString("NOME_VAGA")));
				vaga.setStatusVaga(rs.getShort("STATUS_VAGA"));

				listaVagas.add(vaga);

			}

			rs.close();
			stmt.close();

			return listaVagas;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public TipoVaga retornaTipoVaga(long codigoTipoVaga) {
		try {
			String sql = "SELECT * FROM TIPO_VAGA WHERE COD_TIPO_VAGA = ? LIMIT 1";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, codigoTipoVaga);

			ResultSet rs = stmt.executeQuery();

			TipoVaga tipoVaga = new TipoVaga();

			while (rs.next()) {
				tipoVaga.setCodigoTipoVaga(rs.getLong("COD_TIPO_VAGA"));
				tipoVaga.setDescricaoTipoVaga(rs
						.getString("DESCRICAO_TIPO_VAGA"));

			}

			rs.close();
			stmt.close();

			return tipoVaga;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public void alteraStatusVagaCancelaEntradaPorNomeCupom(long nomeCupom) {
		try {
			
			String sql = "UPDATE VAGA SET STATUS_VAGA = 1 WHERE COD_VAGA = (SELECT COD_VAGA FROM CUPOM WHERE NOME_CUPOM = ?)";

			
			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);
			
			stmt.setLong(1, nomeCupom);

			stmt.execute();
			stmt.close();
			
			

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	
	public boolean verificaExistenciaVagaLivreClienteAvulso() {
		try {
		String sql = "SELECT IF((SELECT count(*) FROM VAGA WHERE STATUS_VAGA = 0 AND COD_TIPO_VAGA = "
				+ "(SELECT COD_TIPO_VAGA FROM TIPO_VAGA WHERE DESCRICAO_TIPO_VAGA = 'CARRO_AVULSO')) > 0,'true','false') AS 'EXISTE_VAGA_AVULSO'";
	
		
		PreparedStatement stmt;
		stmt = connection.prepareStatement(sql);

		ResultSet rs = stmt.executeQuery();
		boolean existeVagaAvulso = false;

		while (rs.next()) {
			existeVagaAvulso = Boolean.valueOf(rs.getString("EXISTE_VAGA_AVULSO"));
		}

		rs.close();
		stmt.close();

		return existeVagaAvulso;

		
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	
	

}
